#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb
import smtp
import time
import os
import sys

def getComments(tableName, cursor) :
    sql = "SELECT DISTINCT(comment) FROM %s" % tableName
    print sql
    cursor.execute(sql)
    comments = cursor.fetchall()
    sorted(comments)
    return comments

def writeDB() :
    db = MySQLdb.connect("localhost", "root", "123456", "peersStat")
    cursor = db.cursor()
    dayTime = time.strftime("%Y_%m_%d", time.localtime())
    tableName = "peers_%s" % dayTime
    reportTableName = "report_%s" % dayTime
    db.set_character_set("utf8")
    cursor.execute("SET NAMES 'utf8'")
    cursor.execute("SET CHARACTER SET 'utf8'")
    cursor.execute("SET character_set_connection='utf8'")

    comments = getComments(tableName, cursor)

    # create table
    columnFmt = " %s int(20) DEFAULT NULL,"
    coCreateStr = ""
    whereCo = ""
    for co in comments :
        coCreateStr += "," + columnFmt % co[0]
        whereCo += " comment='%s' OR" % co[0]
    whereCo += " 0"
    createTbSql = "CREATE TABLE IF NOT EXISTS %s (\
                    X datetime NOT NULL,%s\
                    ) DEFAULT CHARSET=utf8" % (reportTableName, coCreateStr) 
    print createTbSql
    cursor.execute(createTbSql)

    # insert data
    sql = "SELECT DISTINCT(time) FROM %s" % tableName
    cursor.execute(sql)
    time = cursor.fetchall()
    for t in times :
        sql = "SELECT comment, peerSum FROM %s WHERE time='%s' AND%s" % (tableName, t[0], whereCo)
        cursor.execute(sql)
        sumTuple = cursor.fetchall()
        sorted(sumTuple, key=lambda tup : tup[0])
        sumList = []
        sumList.append(str(t[0]))
        coKey = "X"
        for value in sumTuple :
            sumList.append(int(value[1]))
            coKey += "," + value[0]
        rowTuple = tuple(sumList)

        insertSql = "INSERT INTO %s (%s) VALUES %s" % (reportTableName, coKey, rowTuple)
        try :
            cursor.execute(insertSql)
            db.commit()
        except :
            print "insert error"
            db.rollback()

    file = "/tmp/xlsData/%s.xls" % reportTableName
    if os.path.exists(file) :
        os.remove(file)
    sql = "SELECT * FROM %s INTO OUTFILE %s" % (reportTableName, file)
    cursor.execute(sql)
    db.commit()

    db.close()

def selectMax(max=0) :
    db = MySQLdb.connect("localhost", "root", "123456", "peersStat")
    cursor = db.cursor()
    dayTime = time.strftime("%Y_%m_%d", time.localtime())
    tableName = "peers_%s" % dayTime
    reportTableName = "report_%s" % dayTime
    db.set_character_set("utf8")
    cursor.execute("SET NAMES 'utf8'")
    cursor.execute("SET CHARACTER SET 'utf8'")
    cursor.execute("SET character_set_connection='utf8'")

    if max == 0 :
        sql = "SELECT MAX(peerSum) FROM %s" % tableName
        cursor.execute(sql)
        sum = cursor.fetchone()
        maxSum = sum[0]
    else :
        maxSum = int(max)

    sql = "SELECT DISTINCT(comment) FROM %s WHERE peerSum='%d'" % (tableName, maxSum)
    cursor.execute(sql)
    comments = cursor.fetchall()
    sorted(comments)

    # create table
    cursor.execute("DROP TABLE IF EXISTS " + reportTableName)
    columnFmt = " %s int(20) DEFAULT NULL"
    coCreateStr = ""
    whereCo = ""
    for co in comments :
        coCreateStr += "," + columnFmt % co[0]
        whereCo += " comment='%s' OR" % co[0]
    whereCo += " 0"
    # print whereCo
    # print coCreateStr
    # print coKey
    createTbSql = "CREATE TABLE IF NOT EXISTS %s (X datetime NOT NULL%s) DEFAULT CHARSET=utf8" % (reportTableName, coCreateStr) 
    # print createTbSql
    cursor.execute(createTbSql)

    # insert data
    sql = "SELECT DISTINCT(time) FROM %s" % tableName
    cursor.execute(sql)
    times = cursor.fetchall()
    for t in times :
        sql = "SELECT comment, peerSum FROM %s WHERE time='%s' AND(%s)" % (tableName, t[0], whereCo)
        # print sql
        cursor.execute(sql)
        sumTuple = cursor.fetchall()
        sorted(sumTuple, key=lambda tup : tup[0])
        # print sumTuple
        sumList = []
        sumList.append(str(t[0]))
        coKey = "X"
        for value in sumTuple :
            sumList.append(int(value[1]))
            coKey += "," + value[0]
        rowTuple = tuple(sumList)

        insertSql = "INSERT INTO %s (%s) VALUES %s" % (reportTableName, coKey, rowTuple)
        print insertSql
        cursor.execute(insertSql)
        db.commit()
        # try :
        #     cursor.execute(insertSql)
        #     db.commit()
        # except :
        #     print "insert error"
        #     db.rollback()

    file = "/tmp/%s.xls" % reportTableName
    if os.path.exists(file) :
        os.remove(file)
    sql = "SELECT * FROM %s INTO OUTFILE '%s'" % (reportTableName, file)
    cursor.execute(sql)
    dataFd = open(file, "ab")

    sql = "SHOW columns FROM %s" % reportTableName
    cursor.execute(sql)
    cols = cursor.fetchall()
    colStr = ""
    for col in cols :
        colStr += col[0] + "\t"

    dataFd.seek(1)
    dataFd.write(colStr)
    dataFd.flush()
    dataFd.close()
    db.commit()
    os.system("iconv -futf8 -tgb2312 -o %s %s" % (file, file))
    os.system("mysqldump -uroot -p123456 peersStat %s > %s.sql" % (reportTableName, reportTableName))
    curTime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    smtp.report("statistic deadline : %s" % curTime, file, reportTableName + ".sql")

    db.close()

def main(max) :
    # writeDB()
    selectMax(max)

if __name__ == "__main__" :
    if len(sys.argv) <= 1 :
        main(0)
    else :
        main(sys.argv[1])
